import uuid
import os
import json

import requests
import pandas as pd
import mysql.connector
from flask import Flask, session, request, jsonify, render_template, send_from_directory, Response
from werkzeug.utils import secure_filename
# from flask_cors import CORS

app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False
app.config['SECRET_KEY'] = str(uuid.uuid4())
# CORS(app)

# nohup python3 h5Server.py >/dev/null 2>&1

# =====================
# WMS proxy configuration (merged from server.py)
# =====================
REMOTE_BASE = 'http://127.0.0.1:18380/wms-web/invweb/eoOrderInterfaceController'
REMOTE_RECEIVE = 'http://127.0.0.1:18380/wms-web/inbweb/inboundRcvController/receidBySnCode.shtml'
REMOTE_KEY = '223679F'

# Pick related API base
REMOTE_PICK_BASE = 'http://127.0.0.1:18380/wms-web/rfweb/screenSoController'

PROJECT_ROOT = os.path.dirname(os.path.abspath(__file__))

# SN码对比数据库配置
SN_COMPARE_DB_CONFIG = {
    'host': '172.24.222.20',
    'port': 2281,
    'user': 'a_comm_prd',
    'password': 'c4F!9opd',
    'database': 'asd_lmdm_prd',
    'charset': 'utf8mb4'
}

def _make_json_response(content: bytes, status_code: int = 200) -> Response:
    # Try to respect JSON content-type like original server
    response = Response(content, status=status_code)
    response.headers['Content-Type'] = 'application/json; charset=utf-8'
    response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate'
    return response

def _proxy_get(url: str) -> Response:
    try:
        print(f"正在请求远程URL: {url}")
        r = requests.get(url, timeout=15, headers={'Content-Type': 'application/json'})
        print(f"远程响应状态码: {r.status_code}")
        print(f"远程响应内容: {r.text[:500]}...")  # 只打印前500个字符
        return _make_json_response(r.content, r.status_code)
    except requests.HTTPError as e:
        print(f"HTTP错误: {e}")
        return _make_json_response(json.dumps({'code': getattr(e.response, 'status_code', 500), 'msg': str(e)}).encode('utf-8'), getattr(e.response, 'status_code', 500))
    except requests.RequestException as e:
        print(f"请求异常: {e}")
        return _make_json_response(json.dumps({'code': 502, 'msg': f'Bad Gateway: {e}'}).encode('utf-8'), 502)

def _proxy_post(url: str, data_bytes: bytes) -> Response:
    try:
        r = requests.post(url, data=data_bytes, timeout=15, headers={'Content-Type': 'application/json'})
        return _make_json_response(r.content, r.status_code)
    except requests.HTTPError as e:
        return _make_json_response(json.dumps({'code': getattr(e.response, 'status_code', 500), 'msg': str(e)}).encode('utf-8'), getattr(e.response, 'status_code', 500))
    except requests.RequestException as e:
        return _make_json_response(json.dumps({'code': 502, 'msg': f'Bad Gateway: {e}'}).encode('utf-8'), 502)

@app.route("/upload/MaterialImgUrl")
def index():
    key=request.args.get("key")
    if key !='Rzd8xG0az7rfTy4npW':
        return jsonify({"error": '无权限'}), 401
    session.permanent = True
    session['url'] = str(request.base_url)
    return render_template("index.html")


@app.route('/upload/queryMaterialImgUrl/<path:url>', methods=['GET', 'POST', 'PUT', 'DELETE'])
def proxy(url):
    # 设置请求头允许跨域访问
    response_headers = {
        "Access-Control-Allow-Origin": "*",
        "Access-Control-Allow-Methods": "GET, POST, PUT, DELETE",
        "Access-Control-Allow-Headers": "Content-Type"
    }

    query_params = request.args.to_dict()

    # 发送请求到目标URL
    try:
        resp = requests.request(
            method=request.method,
            url=f"https://www.anyserves56.com/lmdm-web/bdmweb/cdCommonMaterialController/{url}",
            # print(url)
            #url=f"{session.get('url')}{url}",
            headers={key: value for (key, value) in request.headers if key != 'Host'},
            params=query_params,
            data=request.get_data(),
            cookies=request.cookies,
            allow_redirects=False)

        # 返回响应给客户端
        excluded_headers = ['content-encoding', 'content-length', 'transfer-encoding', 'connection']
        headers = [(name, value) for (name, value) in resp.raw.headers.items() if name.lower() not in excluded_headers]
        response = app.response_class(resp.content, resp.status_code, headers)
        # 应用跨域响应头
        for key, value in response_headers.items():
            response.headers[key] = value
        return response
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# =====================
# API endpoints merged from server.py
# =====================
@app.get('/api/test')
def api_test():
    """测试远程服务器连接"""
    test_url = f"{REMOTE_BASE}/findScreenOrderByWhcode.shtml?whCode=ZB001&userCode=Test&key={REMOTE_KEY}"
    print(f"测试连接URL: {test_url}")
    return _proxy_get(test_url)

@app.get('/api/orders')
def api_orders():
    wh_code = request.args.get('whCode')
    user_code = request.args.get('userCode')
    if not wh_code or not user_code:
        return jsonify({'code': 400, 'msg': 'whCode和userCode参数不能为空'}), 400
    url = f"{REMOTE_BASE}/findScreenOrderByWhcode.shtml?whCode={wh_code}&userCode={user_code}&key={REMOTE_KEY}"
    print(f"API请求: whCode={wh_code}, userCode={user_code}")
    print(f"请求URL: {url}")
    return _proxy_get(url)


@app.get('/api/items')
def api_items():
    project_id = request.args.get('projectId', '')
    asn_no = request.args.get('asnNo', '')
    url = f"{REMOTE_BASE}/findScreenSkyByOrder.shtml?projectId={project_id}&asnNo={asn_no}&key={REMOTE_KEY}"
    return _proxy_get(url)


@app.get('/api/pendingSNs')
def api_pending_sns():
    project_id = request.args.get('projectId', '')
    asn_no = request.args.get('asnNo', '')
    sku_code = request.args.get('skuCode', '')
    url = f"{REMOTE_BASE}/findScreenSnBySku.shtml?projectId={project_id}&asnNo={asn_no}&skuCode={sku_code}&key={REMOTE_KEY}"
    return _proxy_get(url)


@app.get('/api/pickOrders')
def api_pick_orders():
    wh_code = request.args.get('whCode')
    user_code = request.args.get('userCode')
    if not wh_code or not user_code:
        return jsonify({'code': 400, 'msg': 'whCode和userCode参数不能为空'}), 400
    url = f"{REMOTE_PICK_BASE}/pickScreenListByWhcode.shtml?whCode={wh_code}&userCode={user_code}"
    return _proxy_get(url)


@app.get('/api/pickItems')
def api_pick_items():
    project_id = request.args.get('projectId', '')
    so_no = request.args.get('soNo', '')
    user_code = request.args.get('userCode', '')
    url = f"{REMOTE_PICK_BASE}/pickSkuListByOrder.shtml?projectId={project_id}&soNo={so_no}&userCode={user_code}"
    return _proxy_get(url)


@app.post('/api/receive')
def api_receive():
    body = request.get_data() or b'{}'
    print(f"收到收货请求: {body.decode('utf-8', errors='ignore')}")
    return _proxy_post(REMOTE_RECEIVE, body)


@app.post('/api/pickBySn')
def api_pick_by_sn():
    body = request.get_data() or b'{}'
    url = f"{REMOTE_PICK_BASE}/pkBySnCode"
    return _proxy_post(url, body)


# =====================
# 添加 /upload/api/ 路径的API端点（为了兼容Nginx配置）
# =====================
@app.get('/upload/api/test')
def upload_api_test():
    """测试远程服务器连接 - /upload/api/ 路径版本"""
    test_url = f"{REMOTE_BASE}/findScreenOrderByWhcode.shtml?whCode=ZB001&userCode=Test&key={REMOTE_KEY}"
    print(f"测试连接URL: {test_url}")
    return _proxy_get(test_url)

@app.get('/upload/api/orders')
def upload_api_orders():
    wh_code = request.args.get('whCode')
    user_code = request.args.get('userCode')
    if not wh_code or not user_code:
        return jsonify({'code': 400, 'msg': 'whCode和userCode参数不能为空'}), 400
    url = f"{REMOTE_BASE}/findScreenOrderByWhcode.shtml?whCode={wh_code}&userCode={user_code}&key={REMOTE_KEY}"
    print(f"API请求: whCode={wh_code}, userCode={user_code}")
    print(f"请求URL: {url}")
    return _proxy_get(url)

@app.get('/upload/api/items')
def upload_api_items():
    project_id = request.args.get('projectId', '')
    asn_no = request.args.get('asnNo', '')
    url = f"{REMOTE_BASE}/findScreenSkyByOrder.shtml?projectId={project_id}&asnNo={asn_no}&key={REMOTE_KEY}"
    return _proxy_get(url)

@app.get('/upload/api/pendingSNs')
def upload_api_pending_sns():
    project_id = request.args.get('projectId', '')
    asn_no = request.args.get('asnNo', '')
    sku_code = request.args.get('skuCode', '')
    url = f"{REMOTE_BASE}/findScreenSnBySku.shtml?projectId={project_id}&asnNo={asn_no}&skuCode={sku_code}&key={REMOTE_KEY}"
    return _proxy_get(url)

@app.get('/upload/api/pickOrders')
def upload_api_pick_orders():
    wh_code = request.args.get('whCode')
    user_code = request.args.get('userCode')
    if not wh_code or not user_code:
        return jsonify({'code': 400, 'msg': 'whCode和userCode参数不能为空'}), 400
    url = f"{REMOTE_PICK_BASE}/pickScreenListByWhcode.shtml?whCode={wh_code}&userCode={user_code}"
    return _proxy_get(url)

@app.get('/upload/api/pickItems')
def upload_api_pick_items():
    project_id = request.args.get('projectId', '')
    so_no = request.args.get('soNo', '')
    user_code = request.args.get('userCode', '')
    url = f"{REMOTE_PICK_BASE}/pickSkuListByOrder.shtml?projectId={project_id}&soNo={so_no}&userCode={user_code}"
    return _proxy_get(url)

@app.post('/upload/api/receive')
def upload_api_receive():
    body = request.get_data() or b'{}'
    print(f"收到收货请求: {body.decode('utf-8', errors='ignore')}")
    return _proxy_post(REMOTE_RECEIVE, body)

@app.post('/upload/api/pickBySn')
def upload_api_pick_by_sn():
    body = request.get_data() or b'{}'
    url = f"{REMOTE_PICK_BASE}/pkBySnCode"
    return _proxy_post(url, body)


# =====================
# SN码对比相关API
# =====================
@app.post('/upload/api/compareSn')
def api_compare_sn():
    """处理Excel上传并对比SN码"""
    try:
        if 'file' not in request.files:
            return jsonify({'error': '未上传文件'}), 400
        
        file = request.files['file']
        if file.filename == '':
            return jsonify({'error': '未选择文件'}), 400
        
        # 读取Excel文件
        try:
            df = pd.read_excel(file)
        except Exception as e:
            return jsonify({'error': f'读取Excel文件失败: {str(e)}'}), 400
        
        # 检查必需的列
        required_columns = ['客户单号', '商品代码', 'SN码']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            return jsonify({'error': f'Excel文件缺少必需的列: {", ".join(missing_columns)}'}), 400
        
        # 验证必填字段 - 检查是否有空值
        invalid_mask = df[['客户单号', '商品代码', 'SN码']].isnull().any(axis=1)
        invalid_rows = df[invalid_mask]
        if not invalid_rows.empty:
            return jsonify({'error': f'发现 {len(invalid_rows)} 行存在必填字段为空，请检查Excel文件'}), 400
        
        # 过滤掉空值行
        df = df.dropna(subset=['客户单号', '商品代码', 'SN码'])
        if df.empty:
            return jsonify({'error': 'Excel文件中没有有效数据'}), 400
        
        # 按客户单号和商品代码汇总SN码
        grouped = df.groupby(['客户单号', '商品代码'])['SN码'].apply(list).reset_index()
        
        # 连接数据库进行对比
        conn = None
        cursor = None
        differences = []
        
        try:
            conn = mysql.connector.connect(**SN_COMPARE_DB_CONFIG)
            cursor = conn.cursor(dictionary=True)
            
            for _, row in grouped.iterrows():
                trace_id = str(row['客户单号']).strip()
                sku_code = str(row['商品代码']).strip()
                excel_sns = [str(sn).strip() for sn in row['SN码'] if pd.notna(sn)]
                
                # 查询数据库
                query = """
                    SELECT
                        wss.SO_NO,
                        wss.SERIAL_NO,
                        wss.LOT_NUM
                    FROM
                        wm_so_serial wss
                    WHERE
                        wss.TRACE_ID = %s
                        AND wss.SKU_CODE = %s
                """
                cursor.execute(query, (trace_id, sku_code))
                db_results = cursor.fetchall()
                
                db_sns = [r['SERIAL_NO'] for r in db_results if r['SERIAL_NO']]
                lot_num = db_results[0]['LOT_NUM'] if db_results else None
                
                # 对比SN码
                excel_set = set(excel_sns)
                db_set = set(db_sns)
                
                if excel_set != db_set:
                    differences.append({
                        'trace_id': trace_id,
                        'sku_code': sku_code,
                        'lot_num': lot_num,
                        'excel_sns': excel_sns,
                        'db_sns': db_sns,
                        'excel_only': list(excel_set - db_set),
                        'db_only': list(db_set - excel_set)
                    })
            
            return jsonify({
                'total': len(grouped),
                'has_differences': len(differences) > 0,
                'differences': differences
            })
            
        except mysql.connector.Error as e:
            return jsonify({'error': f'数据库查询失败: {str(e)}'}), 500
        finally:
            if cursor:
                cursor.close()
            if conn and conn.is_connected():
                conn.close()
                
    except Exception as e:
        return jsonify({'error': f'处理失败: {str(e)}'}), 500


@app.post('/upload/api/executeSnUpdate')
def api_execute_sn_update():
    """执行SN码更新"""
    try:
        data = request.get_json()
        if not data or 'differences' not in data:
            return jsonify({'error': '缺少必要参数'}), 400
        
        differences = data['differences']
        if not differences:
            return jsonify({'error': '没有需要更新的数据'}), 400
        
        conn = None
        cursor = None
        updated_count = 0
        
        try:
            conn = mysql.connector.connect(**SN_COMPARE_DB_CONFIG)
            cursor = conn.cursor()
            
            for diff in differences:
                trace_id = diff.get('trace_id', '').strip()
                sku_code = diff.get('sku_code', '').strip()
                excel_sns = diff.get('excel_sns', [])
                db_sns = diff.get('db_sns', [])
                
                # 找出Excel中有但数据库中没有的SN码
                excel_only = [sn for sn in excel_sns if sn not in db_sns]
                # 找出数据库中有但Excel中没有的SN码
                db_only = [sn for sn in db_sns if sn not in excel_sns]
                
                # 建立对应关系：将db_only中的SN码更新为excel_only中的SN码
                # 按顺序对应，取较小的数量
                pair_count = min(len(excel_only), len(db_only))
                
                for i in range(pair_count):
                    old_sn = db_only[i]  # 数据库中的旧SN码
                    new_sn = excel_only[i]  # Excel中的新SN码
                    
                    update_sql = """
                        UPDATE wm_so_serial 
                        SET SERIAL_NO = %s, 
                            SCAN_OP = 'systemModiry', 
                            SCAN_TIME = NOW() 
                        WHERE TRACE_ID = %s 
                            AND SKU_CODE = %s 
                            AND SERIAL_NO = %s
                    """
                    cursor.execute(update_sql, (new_sn, trace_id, sku_code, old_sn))
                    if cursor.rowcount > 0:
                        updated_count += 1
            
            conn.commit()
            
            return jsonify({
                'success': True,
                'updated_count': updated_count,
                'msg': f'成功更新 {updated_count} 条记录'
            })
            
        except mysql.connector.Error as e:
            if conn:
                conn.rollback()
            return jsonify({'error': f'数据库更新失败: {str(e)}'}), 500
        finally:
            if cursor:
                cursor.close()
            if conn and conn.is_connected():
                conn.close()
                
    except Exception as e:
        return jsonify({'error': f'处理失败: {str(e)}'}), 500


# =====================
# Static files previously served by server.py (placed last to avoid shadowing)
# =====================
@app.route('/')
def root_index():
    default_page = 'receive_goods.html'
    if os.path.exists(os.path.join(PROJECT_ROOT, default_page)):
        return send_from_directory(PROJECT_ROOT, default_page)
    return jsonify({'msg': 'H5 Server running'}), 200

# 添加 /upload/ 路径下的静态文件路由
@app.route('/upload/<path:filename>')
def serve_upload_static(filename):
    file_path = os.path.join(PROJECT_ROOT, filename)
    if os.path.isfile(file_path):
        return send_from_directory(PROJECT_ROOT, filename)
    return jsonify({'code': 404, 'msg': 'File not found'}), 404

@app.route('/<path:filename>')
def serve_static_root(filename):
    file_path = os.path.join(PROJECT_ROOT, filename)
    if os.path.isfile(file_path):
        return send_from_directory(PROJECT_ROOT, filename)
    return jsonify({'code': 404, 'msg': 'Not Found'}), 404


if __name__ == "__main__":
    # app.run(
    #     debug=False,
    #     threaded=True,
    #     host="0.0.0.0",
    #     port=1377,
    #     ssl_context=('cert.pem', 'key.pem')
    # )
    app.run(
        debug=True,
        threaded=True,
        host="0.0.0.0",
        port=9004
    )
